#encoding=utf8
import MySQLdb
import pinyin


def convert_location_db():
  """将旧的数据库结构中的地点数据导入到新的结构中"""
  conn = get_connection()
  cursor = conn.cursor()
  cursor.execute("set names utf8")
  cursor.execute("truncate table location")
  cursor.execute("select AREA_ID, P_NAME, CITY_NAME, AREA_NAME from basic_province_city")
  result = []
  auto_id = 1

  temp = {}
  for aid, provice, city, area in cursor.fetchall():
    if temp.has_key(provice):
      pid = temp[provice]
    else:
      cursor.execute("insert into location (name, parent_id) values (%s, 0)", provice)
      cursor.execute("select last_insert_id()")
      pid = cursor.fetchone()[0]
      temp[provice] = pid
      

    if city == "县" or city == "市辖区":
      city = area
      area = None
    
    key = "%d\n%s" % (pid, city)
    if temp.has_key(key):
      cid = temp[key]
    else:
      cursor.execute("insert into location (name, parent_id) values (%s, %s)", (city, pid))
      cursor.execute("select last_insert_id()")
      cid = cursor.fetchone()[0]
      temp[key] = cid
    
    if not area: continue

    cursor.execute("insert into location (name, parent_id) values (%s, %s)", (area, cid))    
  conn.close()

def convert_location_php():
  """生成LocationLib.php的数据部分"""
  out = open("LocationLib.php", "w")
  
  conn = get_connection()
  cursor = conn.cursor()
  cursor.execute("set names utf8")
  cursor.execute("select location_id, name, parent_id from location order by seq, location_id")
  temp = {}
  result = cursor.fetchall()
  conn.close()

  for location_id, name, parent_id in result:
    if not temp.has_key(parent_id):
      temp[parent_id] = []

    temp[parent_id].append((location_id, name))
  
  for id in temp.keys():
    out.write("\t\t%d => array(" %id)
    started = False
    for lid, name in temp[id]:
      if not started: started = True
      else: out.write(",")

      out.write("%d" % lid)
    out.write("),\n")

  out.write("\n\n\t\t")
  for location_id, name, parent_id in result:
    out.write("%d=>array(\"%s\", %d), " % (location_id, name, parent_id))
  out.close()


def convert_location_js():
  """生成地点列表的JS文件"""
  out = open("location.lib.js", "w")
  
  conn = get_connection()
  cursor = conn.cursor()
  cursor.execute("set names utf8")
  cursor.execute("select location_id, name, parent_id from location order by seq, location_id")
  temp = {}
  result = cursor.fetchall()
  conn.close()

  started = False
  
  out.write("var _locations=[")
  for location_id, name, parent_id in result:
    if started: out.write(",")
    else: started = True
    out.write("[%d,\"%s\",%d]" % (location_id, name, parent_id))
  out.write("];")
  out.close()


def convert_university():
  """将旧的数据库结构中的大学数据导入到新的结构中"""
  conn = get_connection()
  cursor = conn.cursor()
  cursor.execute("set names utf8")
  cursor.execute("truncate table university")
  cursor.execute("truncate table university_major")
  cursor.execute("select M_ID, P_NAME, U_NAME, M_NAME from basic_univs_major")

  data = cursor.fetchall()
  universities = {}

  for uid, pname, name, mname in data:
    if universities.has_key(pname + "\n" + name):
      uid = universities[pname + "\n" + name]
    else:
      cursor.execute("select location_id from location where name=%s order by parent_id ", pname)
      row = cursor.fetchone()
      if row:
        lid = row[0]
      else:
        lid = 0
	print "bad location:%d" % uid

      cursor.execute("insert into university (name, location_id) values (%s, %s)", (name, lid))
      cursor.execute("select last_insert_id()")
      uid = cursor.fetchone()[0]
      universities[pname + "\n" + name] = uid
    

    cursor.execute("insert into university_major (name, university_id) values (%s, %s)", (mname, uid))

  conn.close()


def convert_university_php():
  """生成UniversityLib.php的数据部分"""
  out = open("UniversityLib.php", "w")
  
  conn = get_connection()
  cursor = conn.cursor()
  cursor.execute("set names utf8")
  cursor.execute("select university_id, name, location_id from university order by seq, university_id")
  result = cursor.fetchall()
  conn.close()

  temp = {}

  for university_id, name, location_id in result:
    out.write("%d=>\"%s\", " % (university_id, name))
    if not temp.has_key(location_id):
      temp[location_id] = []
    temp[location_id].append(university_id)
  
  out.write("\n\n")
  for location_id in temp.keys():
    out.write("\t\t%d => array(" % location_id)
    started = False
    for uid in temp[location_id]:
      if not started: started = True
      else: out.write(",")

      out.write("%d" % uid)
    out.write("),\n")
    
  out.close()

def convert_university_js():
  """生成大学列表的JS文件"""
  out = open("universitylib.js", "w")
    
  conn = get_connection()
  cursor = conn.cursor()
  cursor.execute("set names utf8")
  cursor.execute("select university_id, name from university order by seq, university_id")
  temp = {}
  result = cursor.fetchall()
  conn.close()

  started = False
  
  out.write("var _universities=[")
  for university_id, name in result:
    if started: out.write(",")
    else: started = True
    out.write("[%d,\"%s\",\"%s\\n%s\"]" % (university_id, name, pinyin.toPinYin(name), pinyin.toPinShou(name)))
  out.write("];")
  out.close()

def get_connection():
  """获取数据库连接"""
  host = '127.0.0.1'     
  name = 'uc_profile'    
  user = 'root'    
  password = 'root' 
  return MySQLdb.connect(host, user, password, name)


if __name__ == "__main__":
  convert_location_js()
  convert_university_js()